package com.gemptc.yueban.daoImp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.gemptc.bean.ActivityBean;
import com.gemptc.daoimp.SelectImp;
import com.gemptc.utils.DBUtil;
import com.gemptc.utils.DataUtil;
import com.gemptc.yueban.dao.ISelectActivity;

public class SelectActivityImp implements ISelectActivity {

	// 查找所有约伴的数据接口

	public List<ActivityBean> getAllBean(int pageNow) {
		Connection con = DBUtil.getConnection();
		PreparedStatement pre = null;
		ResultSet res = null;
		List<ActivityBean> list = new ArrayList<ActivityBean>();
		int m = (pageNow - 1) * DataUtil.pageNum;
		int n = DataUtil.pageNum;
		String sql = "select * from activity ORDER BY ac_id DESC limit ?,?";
		try {
			pre = con.prepareStatement(sql);
			pre.setInt(1, m);
			pre.setInt(2, n);
			// 查找到数据并 添加
			res = pre.executeQuery();
			// 遍历循环并得到数组list里的数据
			while (res.next()) {
				// 查找到表activity里的第1列数据ac_id
				int ac_id = res.getInt(1);
				// 用SelectUserName方法通过 activity里user_id查找user表里的user_id,
				// 查找到user表里的user_nickname，
				int user_id = res.getInt(2);
				SelectImp nickname = new SelectImp();
				String user_nickname = nickname.select(user_id);
				// 查找到表activity里的第3列数据字符串类型的t_title
				String ac_title = res.getString(3);
				// 查找到表activity里的第5列数据字符串类型的ac_time
				String ac_time = res.getString(4);
				// 查找到表activity里的第5列数据字符串类型的ac_lastTime
				String ac_lastTime = res.getString(5);
				// 查找到表activity里的第6列数据字符串类型的ac_venue
				String ac_venue = res.getString(6);
				// 查找到表activity里的第7列数据字符串类型的ac_venue_longitude
				String ac_venue_longitude = res.getString(7);
				// 查找到表activity里的第8列数据字符串类型的ac_venue_latitude
				String ac_venue_latitude = res.getString(8);
				// 查找到表activity里的第9列数据字符串类型的ac_destination
				String ac_destination = res.getString(9);
				// 查找到表activity里的第10列数据字符串类型的ac_destination_longitude
				String ac_destination_longitude = res.getString(10);
				// 查找到表activity里的第11列数据字符串类型的ac_destination_latitude
				String ac_destination_latitude = res.getString(11);
				// 查找到表activity里的第12列数据ac_contact
				String ac_contact = res.getString(12);
				// 查找到表activity里的第13列数据字符串类型的ac_content
				String ac_content = res.getString(13);
				// 查找到表activity里的第14列数据ac_number
				int ac_number = res.getInt(14);
				// 求已报名总人数
				SelectRegistrationImp sumImp = new SelectRegistrationImp();
				int ac_number_sum = sumImp.getSum(ac_id);
				// 查找到表activity里的第15列数据字符串类型的ac_pic_path
				String ac_pic_path = res.getString(15);
				String ac_date = res.getString(16);

				ActivityBean activityBean = new ActivityBean(ac_id, user_id,
						ac_contact, ac_number, ac_number_sum, user_nickname,
						ac_title, ac_time, ac_lastTime, ac_venue,
						ac_venue_longitude, ac_venue_latitude, ac_destination,
						ac_destination_longitude, ac_destination_latitude,
						ac_content, ac_pic_path, ac_date);
				list.add(activityBean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.release(res, pre, con);
		}
		return list;
	}

	// 查找所有约伴的数据接口115

	public List<ActivityBean> getAll(int user_id) {
		Connection con = DBUtil.getConnection();
		PreparedStatement pre = null;
		ResultSet res = null;
		List<ActivityBean> list = new ArrayList<ActivityBean>();
		// int m = (pageNow - 1) * DataUtil.pageNum;
		// int n = DataUtil.pageNum;
		String sql = "select * from activity where user_id=?";
		try {
			pre = con.prepareStatement(sql);
			pre.setInt(1, user_id);

			// 查找到数据并 添加
			res = pre.executeQuery();
			// 遍历循环并得到数组list里的数据
			while (res.next()) {
				// 查找到表activity里的第1列数据ac_id
				int ac_id = res.getInt(1);
				// 用SelectUserName方法通过 activity里user_id查找user表里的user_id,
				// 查找到user表里的user_nickname，
				int userid = res.getInt(2);
				SelectImp nickname = new SelectImp();
				String user_nickname = nickname.select(userid);
				// 查找到表activity里的第3列数据字符串类型的t_title
				String ac_title = res.getString(3);
				// 查找到表activity里的第5列数据字符串类型的ac_time
				String ac_time = res.getString(4);
				// 查找到表activity里的第5列数据字符串类型的ac_lastTime
				String ac_lastTime = res.getString(5);
				// 查找到表activity里的第6列数据字符串类型的ac_venue
				String ac_venue = res.getString(6);
				// 查找到表activity里的第7列数据字符串类型的ac_venue_longitude
				String ac_venue_longitude = res.getString(7);
				// 查找到表activity里的第8列数据字符串类型的ac_venue_latitude
				String ac_venue_latitude = res.getString(8);
				// 查找到表activity里的第9列数据字符串类型的ac_destination
				String ac_destination = res.getString(9);
				// 查找到表activity里的第10列数据字符串类型的ac_destination_longitude
				String ac_destination_longitude = res.getString(10);
				// 查找到表activity里的第11列数据字符串类型的ac_destination_latitude
				String ac_destination_latitude = res.getString(11);
				// 查找到表activity里的第12列数据ac_contact
				String ac_contact = res.getString(12);
				// 查找到表activity里的第13列数据字符串类型的ac_content
				String ac_content = res.getString(13);
				// 查找到表activity里的第14列数据ac_number
				int ac_number = res.getInt(14);
				// 求已报名总人数
				SelectRegistrationImp sumImp = new SelectRegistrationImp();
				int ac_number_sum = sumImp.getSum(ac_id);
				// 查找到表activity里的第15列数据字符串类型的ac_pic_path
				String ac_pic_path = res.getString(15);
				String ac_date = res.getString(16);

				ActivityBean activityBean = new ActivityBean(ac_id, userid,
						ac_contact, ac_number, ac_number_sum, user_nickname,
						ac_title, ac_time, ac_lastTime, ac_venue,
						ac_venue_longitude, ac_venue_latitude, ac_destination,
						ac_destination_longitude, ac_destination_latitude,
						ac_content, ac_pic_path, ac_date);
				System.out.println("1------------>" + activityBean);
				list.add(activityBean);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.release(res, pre, con);
		}
		return list;
	}

	// 115查询 我报名的活动

	public List<ActivityBean> getwobaoming(int user_id) {
		Connection con = DBUtil.getConnection();
		PreparedStatement pre = null;
		ResultSet res = null;
		List<ActivityBean> list = new ArrayList<ActivityBean>();

		String sql = "SELECT  activity.`ac_id`,activity.`user_id`,"
				+ "activity.`ac_title`,activity.`ac_time`, "
				+ "activity.`ac_lastTime`,activity.`ac_number`,activity.`ac_venue`,"
				+ "activity.`ac_content`,activity.`ac_pic_path`,activity.`ac_date`,"
				+ "registration_list.`rl_number` "
				+ "FROM activity ,registration_list "
				+ "WHERE registration_list.`ac_id`=activity.`ac_id`"
				+ " AND registration_list.`user_id`=?;";
		try {
			pre = con.prepareStatement(sql);
			pre.setInt(1, user_id);

			// 查找到数据并 添加
			res = pre.executeQuery();
			// 遍历循环并得到数组list里的数据
			while (res.next()) {
				// 查找到表activity里的第1列数据ac_id
				int ac_id = res.getInt(1);
				// 用SelectUserName方法通过 activity里user_id查找user表里的user_id,
				// 查找到user表里的user_nickname，
				int userid = res.getInt(2);
				SelectImp nickname = new SelectImp();
				String user_nickname = nickname.select(userid);
				// 查找到表activity里的第3列数据字符串类型的t_title
				String ac_title = res.getString(3);
				// 查找到表activity里的第5列数据字符串类型的ac_time
				String ac_time = res.getString(4);
				// 查找到表activity里的第5列数据字符串类型的ac_lastTime
				String ac_lastTime = res.getString(5);
				// 查找到表activity里的第6列数据ac_number
				int ac_number = res.getInt(6);
				// 查找到表activity里的第6列数据字符串类型的ac_venue
				String ac_venue = res.getString(7);
				// 查找到表activity里的第12列数据ac_contact
				// int ac_contact = res.getInt(7);
				// 查找到表activity里的第8列数据字符串类型的ac_content
				String ac_content = res.getString(8);

				// 求已报名总人数
				SelectRegistrationImp sumImp = new SelectRegistrationImp();
				int ac_number_sum = sumImp.getSum(ac_id);
				// 查找到表activity里的第9列数据字符串类型的ac_pic_path
				String ac_pic_path = res.getString(9);
				String ac_date = res.getString(10);

				int rl_number = res.getInt(11);
				// String user_imgPath = res.getString(12);
				ActivityBean activityBean = new ActivityBean(ac_id, user_id,
						ac_number, ac_number_sum, user_nickname, ac_title,
						ac_time, ac_lastTime, ac_venue, ac_content,
						ac_pic_path, ac_date, rl_number);
				System.out.println("32333------------>" + activityBean);
				list.add(activityBean);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.release(res, pre, con);
		}
		return list;
	}

	//删除我报名的活动 11/11
		public boolean deletbaoming(int user_id,int ac_id){
			Connection connection = DBUtil.getConnection();
			PreparedStatement preparedStatement = null;
			
			boolean falg = false;
			String sql = "DELETE  FROM registration_list WHERE user_id=? AND ac_id=?;";
			try {
				preparedStatement = connection.prepareStatement(sql);
				preparedStatement.setInt(1, user_id);
				preparedStatement.setInt(2, ac_id);
				preparedStatement.execute();
				falg = true;
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				DBUtil.release(null, preparedStatement, connection);
			}
			
			return falg;
			
		} 
	
	// 查找热门约伴的数据接口

	public List<ActivityBean> getHotBean(int pageNow) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<ActivityBean> list = new ArrayList<ActivityBean>();
		int m = (pageNow - 1) * DataUtil.pageNum;
		int n = DataUtil.pageNum;
		String sql = "SELECT a.* FROM activity AS a,(SELECT ac_destination,COUNT(ac_destination) "
				+ "AS counts FROM activity GROUP BY ac_destination) "
				+ "AS b WHERE a.ac_destination = b.ac_destination ORDER BY b.counts DESC limit ?,?";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, m);
			preparedStatement.setInt(2, n);
			// 查找到数据并 添加
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				// 查找到表activity里的第1列数据ac_id
				int ac_id = resultSet.getInt(1);
				// 用SelectUserName方法通过 activity里user_id查找user表里的user_id,
				// 查找到user表里的user_nickname，
				int user_id = resultSet.getInt(2);
				SelectImp nickname = new SelectImp();
				String user_nickname = nickname.select(user_id);
				// 查找到表activity里的第3列数据字符串类型的t_title
				String ac_title = resultSet.getString(3);
				// 查找到表activity里的第5列数据字符串类型的ac_time
				String ac_time = resultSet.getString(4);
				// 查找到表activity里的第5列数据字符串类型的ac_lastTime
				String ac_lastTime = resultSet.getString(5);
				// 查找到表activity里的第6列数据字符串类型的ac_venue
				String ac_venue = resultSet.getString(6);
				// 查找到表activity里的第9列数据字符串类型的ac_destination
				String ac_destination = resultSet.getString(9);
				// 查找到表activity里的第12列数据ac_contact
				String ac_contact = resultSet.getString(12);
				// 查找到表activity里的第13列数据字符串类型的ac_content
				String ac_content = resultSet.getString(13);
				// 查找到表activity里的第14列数据ac_number
				int ac_number = resultSet.getInt(14);
				// 求已报名总人数
				SelectRegistrationImp sumImp = new SelectRegistrationImp();
				int ac_number_sum = sumImp.getSum(ac_id);
				// 查找到表activity里的第15列数据字符串类型的ac_pic_path
				String ac_pic_path = resultSet.getString(15);
				String ac_date = resultSet.getString(16);

				ActivityBean activityBean = new ActivityBean(ac_id, user_id,
						ac_contact, ac_number, ac_number_sum, user_nickname,
						ac_title, ac_time, ac_lastTime, ac_venue,
						ac_destination, ac_content, ac_pic_path, ac_date);
				list.add(activityBean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return list;
	}

	// 查找周末约伴的数据接口

	public List<ActivityBean> getWeekBean(int pageNow) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<ActivityBean> list = new ArrayList<ActivityBean>();
		int m = (pageNow - 1) * DataUtil.pageNum;
		int n = DataUtil.pageNum;
		String sql = "SELECT *FROM activity WHERE WEEKDAY(ac_time)=5 "
				+ "AND WEEKDAY(ac_lastTime)=6 ORDER BY ac_id DESC limit ?,?";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, m);
			preparedStatement.setInt(2, n);
			// 查找到数据并 添加
			resultSet = preparedStatement.executeQuery();
			// 遍历循环并得到数组list里的数据
			while (resultSet.next()) {
				// 查找到表activity里的第1列数据ac_id
				int ac_id = resultSet.getInt(1);
				// 用SelectUserName方法通过 activity里user_id查找user表里的user_id,
				// 查找到user表里的user_nickname，
				int user_id = resultSet.getInt(2);
				SelectImp nickname = new SelectImp();
				String user_nickname = nickname.select(user_id);
				// 查找到表activity里的第3列数据字符串类型的t_title
				String ac_title = resultSet.getString(3);
				// 查找到表activity里的第5列数据字符串类型的ac_time
				// 因为ac_time是日期类，需要通过Timestamp转换成字符串类型
				String ac_time = resultSet.getString(4);
				// 查找到表activity里的第5列数据字符串类型的ac_lastTime
				String ac_lastTime = resultSet.getString(5);
				// 查找到表activity里的第6列数据字符串类型的ac_venue
				String ac_venue = resultSet.getString(6);
				// 查找到表activity里的第9列数据字符串类型的ac_destination
				String ac_destination = resultSet.getString(9);
				// 查找到表activity里的第12列数据ac_contact
				String  ac_contact = resultSet.getString(12);
				// 查找到表activity里的第13列数据字符串类型的ac_content
				String ac_content = resultSet.getString(13);
				// 查找到表activity里的第14列数据ac_number
				int ac_number = resultSet.getInt(14);
				// 求已报名总人数
				SelectRegistrationImp sumImp = new SelectRegistrationImp();
				int ac_number_sum = sumImp.getSum(ac_id);
				// 查找到表activity里的第15列数据字符串类型的ac_pic_path
				String ac_pic_path = resultSet.getString(15);
				String ac_date = resultSet.getString(16);
				ActivityBean activityBean = new ActivityBean(ac_id, user_id,
						ac_contact, ac_number, ac_number_sum, user_nickname,
						ac_title, ac_time, ac_lastTime, ac_venue,
						ac_destination, ac_content, ac_pic_path, ac_date);
				list.add(activityBean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return list;
	}

	public List<ActivityBean> getPeopleBean(int pageNow) {
		Connection con = DBUtil.getConnection();
		PreparedStatement pre = null;
		ResultSet res = null;
		List<ActivityBean> list = new ArrayList<ActivityBean>();
		int m = (pageNow - 1) * DataUtil.pageNum;
		int n = DataUtil.pageNum;
		String sql = "SELECT * FROM activity WHERE ac_time > NOW()  ORDER BY ac_id DESC limit ?,?";
		try {
			pre = con.prepareStatement(sql);
			pre.setInt(1, m);
			pre.setInt(2, n);
			// 查找到数据并 添加
			res = pre.executeQuery();
			// 遍历循环并得到数组list里的数据
			while (res.next()) {
				// 查找到表activity里的第1列数据ac_id
				int ac_id = res.getInt(1);
				// 用Select_WouserImp方法通过 activity里user_id查找user表里的user_id,
				// 查找到user表里的username,user_nicknameuser_imgPath
				int user_id = res.getInt(2);
				SelectImp nickname = new SelectImp();
				String user_nickname = nickname.select(user_id);
				SelectImp img = new SelectImp();
				String user_imgPath = img.select1(user_id);
				// 查找到表activity里的第3列数据字符串类型的t_title
				String ac_title = res.getString(3);
				// 查找到表activity里的第5列数据字符串类型的ac_time
				String ac_time = res.getString(4);
				// 查找到表activity里的第5列数据字符串类型的ac_lastTime
				String ac_lastTime = res.getString(5);
				// 查找到表activity里的第6列数据字符串类型的ac_venue
				String ac_venue = res.getString(6);
				// 查找到表activity里的第9列数据字符串类型的ac_destination
				String ac_destination = res.getString(9);
				// 查找到表activity里的第13列数据字符串类型的ac_content
				String ac_content = res.getString(13);
				// 查找到表activity里的第14列数据ac_number
				int ac_number = res.getInt(14);
				// 求已报名总人数
				SelectRegistrationImp sumImp = new SelectRegistrationImp();
				int ac_number_sum = sumImp.getSum(ac_id);
				// 查找到表activity里的第15列数据字符串类型的ac_pic_path
				String ac_pic_path = res.getString(15);

				ActivityBean activityBean = new ActivityBean(ac_id, user_id,
						ac_number, ac_number_sum, user_nickname, user_imgPath,
						ac_title, ac_time, ac_lastTime, ac_venue,
						ac_destination, ac_content, ac_pic_path);
				;
				list.add(activityBean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.release(res, pre, con);
		}

		return list;
	}
}
